"""
Case Type   : Ubtree索引增强
Case Name   : 校验ubtree空间回收_哈希分区表_满足回收条件
Create At   : 2025-11-7
Owner       : sungang14
Description :
    1、创建范围分区表
    2、创建pcr索引
    3、插入测试数据
    4、查询索引文件路径
    5、解析索引页面
    6、删除各分区数据
    7、再次解析索引页面
    8、清理环境
Expect      :
    1、创建成功
    2、成功
    3、成功
    4、成功
    5、解析索引页面成功
    6、删除各分区数据成功
    7、再次解析索引页面成功，索引文件中回收空间
    8、清理环境成功
History     :
"""

import os
import time
import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

logger = Logger()


class SlowSQLTestCase(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.comsh = CommonSH('PrimaryDbUser')
        self.userNode = Node('PrimaryDbUser')
        self.table_name = f"t_ustore_ubtree_0075"
        self.index_name = f"i_ustore_ubtree_0075"
        self.prepare_data_00 = f"drop table if exists {self.table_name} cascade;"
        self.prepare_data_01 = f"""create table {self.table_name} (
                                    order_id bigint,
                                    product_code varchar(20),
                                    quantity int
                                    ) with (storage_type=ustore)
                                        partition by hash (order_id)
                                        partitions 4;"""
        self.prepare_data_02 = f"create index {self.index_name} on {self.table_name}(quantity) local with (index_type='pcr');"
        self.prepare_data_03_01 = f"insert into {self.table_name} select 1001, 'P100', 1 from generate_series(1,300);"
        self.prepare_data_03_02 = f"insert into {self.table_name} select 1001, 'P100', 2 from generate_series(1,300);"
        self.prepare_data_03_03 = f"insert into {self.table_name} select 1001, 'P100', 3 from generate_series(1,300);"
        self.prepare_data_03_04 = f"insert into {self.table_name} select 1002, 'P200', 4 from generate_series(1,350);"
        self.prepare_data_03_05 = f"insert into {self.table_name} select 1002, 'P200', 5 from generate_series(1,350);"
        self.prepare_data_04 = f"SELECT pg_relation_filepath('{self.index_name}');"
        self.prepare_data_05 = f"select relname, relfilenode from pg_partition where parentid = (select oid from pg_class where relname = '{self.index_name}');"
        self.prepare_data_06_01 = f"delete from {self.table_name} where quantity = 1;"
        self.prepare_data_06_02 = f"delete from {self.table_name} where quantity = 2;"
        self.prepare_data_06_03 = f"delete from {self.table_name} where quantity = 3;"
        self.prepare_data_06_04 = f"delete from {self.table_name} where quantity = 4;"
        self.prepare_data_06_05 = f"delete from {self.table_name} where quantity = 5;"
        self.prepare_data_07 = f"insert into {self.table_name} select 1001, 'P100', 1 from generate_series(1,300);"

    def test_slowsql(self):
        text = '--step1:创建范围分区表;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_00)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_01)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, data_cmd, '执行失败' + text)

        text = '--step2:创建pcr索引;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_02)
        logger.info(data_cmd)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG, data_cmd, '执行失败' + text)

        text = '--step3:插入测试数据;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_03_01)
        logger.info(data_cmd)
        self.assertIn(f"INSERT 0 300", data_cmd, '执行失败' + text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_03_02)
        logger.info(data_cmd)
        self.assertIn(f"INSERT 0 300", data_cmd, '执行失败' + text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_03_03)
        logger.info(data_cmd)
        self.assertIn(f"INSERT 0 300", data_cmd, '执行失败' + text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_03_04)
        logger.info(data_cmd)
        self.assertIn(f"INSERT 0 350", data_cmd, '执行失败' + text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_03_05)
        logger.info(data_cmd)
        self.assertIn(f"INSERT 0 350", data_cmd, '执行失败' + text)

        text = '--step4:查询索引文件路径;expect:成功--'
        logger.info(text)
        time.sleep(5)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_04)
        logger.info(data_cmd)
        self.assertIn(f"1 row", data_cmd, '执行失败' + text)
        res_path = data_cmd.splitlines()[-2].strip()
        logger.info(res_path)
        data_cmd5 = self.comsh.execut_db_sql(self.prepare_data_05)
        logger.info(data_cmd5)
        res_path_list = []
        rpcount1_list = []
        for i in range(-5, -1, 1):
            res_path_st = os.path.join(os.path.dirname(res_path), data_cmd5.splitlines()[i].split('|')[1].strip())
            logger.info(res_path_st)
            res_path_list.append(res_path_st)

            text = '--step5:解析索引文件;expect:成功--'
            logger.info(text)
            ph_cmd1 = f"source {macro.DB_ENV_PATH}; cd $PGDATA; pagehack -f {res_path_st} -t ubtree_pcr_index"
            logger.info(ph_cmd1)
            res1 = self.userNode.sh(ph_cmd1).result()
            self.assertNotIn(f'pd_checksum: 0x0, verify fail', res1, '执行失败' + text)
            self.assertRegex(res1, r"pd_checksum: 0x[0-9a-fA-F]+, verify success", '执行失败' + text)
            self.assertNotIn(self.constant.NO_FILE_MSG, res1, '执行失败' + text)
            logger.info(res1.splitlines()[-3].strip())
            rpcount1 = res1.splitlines()[-3].strip().split()[4].strip(',')
            logger.info(rpcount1)
            rpcount1_list.append(int(rpcount1))
        self.assertEqual(sorted(rpcount1_list), [0, 0, 703, 905], '执行失败' + text)

        text = '--step6:删除各分区数据;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_06_01)
        logger.info(data_cmd)
        self.assertIn(f"DELETE 300", data_cmd, '执行失败' + text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_06_02)
        logger.info(data_cmd)
        self.assertIn(f"DELETE 300", data_cmd, '执行失败' + text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_06_03)
        logger.info(data_cmd)
        self.assertIn(f"DELETE 300", data_cmd, '执行失败' + text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_06_04)
        logger.info(data_cmd)
        self.assertIn(f"DELETE 350", data_cmd, '执行失败' + text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_06_05)
        logger.info(data_cmd)
        self.assertIn(f"DELETE 350", data_cmd, '执行失败' + text)
        #
        text = '--step7:再次解析索引文件;expect:已回收--'
        logger.info(text)
        time.sleep(30)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_07)
        logger.info(data_cmd)
        time.sleep(60)
        rpcount2_list = []
        for res_path_st in res_path_list:
            ph_cmd1 = f"source {macro.DB_ENV_PATH}; cd $PGDATA; pagehack -f {res_path_st} -t ubtree_pcr_index"
            logger.info(ph_cmd1)
            res1 = self.userNode.sh(ph_cmd1).result()
            self.assertNotIn(f'pd_checksum: 0x0, verify fail', res1, '执行失败' + text)
            self.assertRegex(res1, r"pd_checksum: 0x[0-9a-fA-F]+, verify success", '执行失败' + text)
            self.assertNotIn(self.constant.NO_FILE_MSG, res1, '执行失败' + text)
            logger.info(res1.splitlines()[-3].strip())
            rpcount2 = res1.splitlines()[-3].strip().split()[4].strip(',')
            logger.info(rpcount2)
            rpcount2_list.append(int(rpcount2))
        self.assertGreater(sorted(rpcount1_list)[-1], sorted(rpcount2_list)[-1], '执行失败' + text)

    def tearDown(self):
        text = '--step8:清理环境，恢复默认值;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_00)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        logger.info(f'-----{os.path.basename(__file__)} end-----')
